<?php
namespace app\index\controller;

use think\Controller;
use think\Db;
use think\facade\Log;
use PhpOffice\PhpSpreadsheet\IOFactory;

/**
 * 简化版Excel处理器 - 专门用于解决UTF-8问题
 */
class ExcelProcessorSimple extends Controller
{
    /**
     * 简化的处理方法
     */
    public function processExcelData()
    {
        // 强制设置UTF-8编码
        header('Content-Type: application/json; charset=utf-8');
        mb_internal_encoding('UTF-8');

        try {
            // 读取Excel文件
            $excelPath = 'F:/phpstudy_pro/intvale/ai_1/doc/sbb.xlsx';

            if (!file_exists($excelPath)) {
                return $this->jsonResponse(404, 'Excel文件不存在', null);
            }

            $spreadsheet = IOFactory::load($excelPath);
            $worksheet = $spreadsheet->getActiveSheet();
            $highestRow = $worksheet->getHighestRow();

            $result = [
                'file_read' => true,
                'total_rows' => $highestRow,
                'processed_rows' => 0,
                'sample_data' => [],
                'errors' => []
            ];

            // 只处理前5行作为测试
            for ($row = 2; $row <= min(6, $highestRow); $row++) {
                try {
                    $rowData = [
                        'A' => $this->cleanUtf8((string)$worksheet->getCell('A' . $row)->getValue()),
                        'B' => $this->cleanUtf8((string)$worksheet->getCell('B' . $row)->getValue()),
                        'C' => $this->cleanUtf8((string)$worksheet->getCell('C' . $row)->getValue()),
                        'D' => $this->cleanUtf8((string)$worksheet->getCell('D' . $row)->getValue())
                    ];

                    if (!empty($rowData['A'])) {
                        $result['sample_data'][] = [
                            'row' => $row,
                            'data' => $rowData
                        ];
                        $result['processed_rows']++;
                    }

                } catch (\Exception $e) {
                    $result['errors'][] = "行 {$row} 处理失败: " . $this->cleanUtf8($e->getMessage());
                }
            }

            return $this->jsonResponse(200, '简化处理完成', $result);

        } catch (\Exception $e) {
            return $this->jsonResponse(500, '处理失败: ' . $this->cleanUtf8($e->getMessage()), null);
        }
    }

    /**
     * 清理UTF-8字符串
     */
    private function cleanUtf8($value)
    {
        if (empty($value)) {
            return '';
        }

        $value = (string)$value;
        $value = trim($value);

        // 检测和转换编码
        if (!mb_check_encoding($value, 'UTF-8')) {
            $value = mb_convert_encoding($value, 'UTF-8', 'auto');
        }

        // 移除无效字符
        $value = preg_replace('/[\x00-\x08\x0B\x0C\x0E-\x1F\x7F]/', '', $value);

        // 确保是有效的UTF-8
        $value = mb_convert_encoding($value, 'UTF-8', 'UTF-8');

        return $value;
    }

    /**
     * 安全的JSON响应
     */
    private function jsonResponse($code, $msg, $data)
    {
        $response = [
            'code' => $code,
            'msg' => $this->cleanUtf8($msg),
            'data' => $this->cleanArrayUtf8($data)
        ];

        // 使用安全的JSON编码
        $json = json_encode($response, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);

        if ($json === false) {
            // 如果JSON编码失败，返回简单响应
            $json = json_encode([
                'code' => 500,
                'msg' => 'JSON编码失败: ' . json_last_error_msg(),
                'data' => null
            ]);
        }

        return response($json)->header('Content-Type', 'application/json; charset=utf-8');
    }

    /**
     * 清理数组中的UTF-8字符
     */
    private function cleanArrayUtf8($data)
    {
        if (is_array($data)) {
            $clean = [];
            foreach ($data as $key => $value) {
                $cleanKey = $this->cleanUtf8($key);
                $clean[$cleanKey] = $this->cleanArrayUtf8($value);
            }
            return $clean;
        } elseif (is_string($data)) {
            return $this->cleanUtf8($data);
        } else {
            return $data;
        }
    }

    /**
     * 测试页面
     */
    public function index()
    {
        echo "<!DOCTYPE html>";
        echo "<html lang='zh-CN'>";
        echo "<head><meta charset='UTF-8'><title>简化Excel处理测试</title></head>";
        echo "<body style='font-family: Arial, sans-serif; margin: 40px;'>";
        echo "<h1>简化Excel处理测试</h1>";
        echo "<button onclick='testProcess()' style='padding: 10px 20px; background: #007bff; color: white; border: none; border-radius: 5px;'>开始测试处理</button>";
        echo "<div id='result' style='margin-top: 20px; padding: 20px; background: #f8f9fa; border-radius: 5px;'></div>";

        echo "<script>";
        echo "function testProcess() {";
        echo "  fetch('/index/excel_processor_simple/processExcelData', { method: 'POST' })";
        echo "    .then(response => response.text())";
        echo "    .then(text => {";
        echo "      try {";
        echo "        const data = JSON.parse(text);";
        echo "        document.getElementById('result').innerHTML = '<pre>' + JSON.stringify(data, null, 2) + '</pre>';";
        echo "      } catch(e) {";
        echo "        document.getElementById('result').innerHTML = '<p style=\"color: red;\">JSON解析失败:</p><pre>' + text + '</pre>';";
        echo "      }";
        echo "    })";
        echo "    .catch(error => {";
        echo "      document.getElementById('result').innerHTML = '<p style=\"color: red;\">请求失败: ' + error.message + '</p>';";
        echo "    });";
        echo "}";
        echo "</script>";

        echo "</body>";
        echo "</html>";
    }
}